﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;

/// <summary>
/// Summary description for CustomRepository
/// </summary>
public static class CustomRepository
{
    public static DataSet CustumDataSetFunction(string storedprocedureName, SqlParameter[] arrParam, string tbl)
    {

        DataSet dset = new DataSet();

        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
        {
            //con.Open();
            SqlCommand cmd = new SqlCommand(storedprocedureName, con);
            cmd.CommandTimeout = 300;
            cmd.CommandType = CommandType.StoredProcedure;
            if (arrParam != null)
            {
                foreach (SqlParameter param in arrParam)
                {
                    cmd.Parameters.Add(param);
                }
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(dset, "tbl");
            }

            //con.Close();

        }
        return dset;



    }
    public static DataSet CustumDataSetFunction(string storedprocedureName, SqlParameter[] arrParam)
    {

        DataSet dset = new DataSet();

        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
        {
            //con.Open();
            SqlCommand cmd = new SqlCommand(storedprocedureName, con);
            cmd.CommandTimeout = 300;
            cmd.CommandType = CommandType.StoredProcedure;
            if (arrParam != null)
            {
                foreach (SqlParameter param in arrParam)
                {
                    cmd.Parameters.Add(param);
                }
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(dset);
            }

            //con.Close();

        }
        return dset;



    }
    public static DataSet CustumDataSetFunction(string sql, string tbl)
    {

        DataSet dset = new DataSet();

        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
        {
            //con.Open();
            SqlCommand cmd = new SqlCommand(sql, con);

            //cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout = 200;
            cmd.CommandText = sql;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            adapter.Fill(dset, tbl);


            //con.Close();

        }
        return dset;



    }

    public static SqlDataReader CustumExecutQuery(string sqlStatement, SqlParameter[] arrParam)
    {

        SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        cnn.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = cnn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = sqlStatement;
        cmd.CommandTimeout = 300;
        if (arrParam != null)
        {
            foreach (SqlParameter param in arrParam)
                cmd.Parameters.Add(param);
        }

        // Execute the reader
        SqlDataReader reader;
        reader = cmd.ExecuteReader();

        return reader;


    }
    public static SqlDataReader CustumExecutQuery(string sqlStatement)
    {

        SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        cnn.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = cnn;
        //cmd.CommandType = CommandType.Text;
        cmd.CommandTimeout = 300;
        cmd.CommandText = sqlStatement;
        //if (arrParam != null)
        //{
        //    foreach (SqlParameter param in arrParam)
        //        cmd.Parameters.Add(param);
        //}

        // Execute the reader
        SqlDataReader reader;
        reader = cmd.ExecuteReader();

        return reader;


    }
    public static int getMaxDootorId()
    {
        DataCalendarDataContext DCD = new DataCalendarDataContext();
        var id = DCD.tbl_Doctors.Max(x=> x.Id);
        //return (from m in DCD.tbl_Doctors
        //        where m

        //        select m).single();

        return id;
    }
    public static int getMaxDentistId()
    {
        DataCalendarDataContext DCD = new DataCalendarDataContext();
        var id = DCD.tbl_Dentists.Max(x => x.Id);
        //return (from m in DCD.tbl_Doctors
        //        where m

        //        select m).single();

        return id;
    }
    public static List<string> getSpecialityId()
    {
        DataCalendarDataContext DCD = new DataCalendarDataContext();
        return (from m in DCD.CanadianDoctors.Select(m => m.Speciality).Distinct()

                select m).ToList();

        //var query = DCD.DentistTests.Select(m => m.Speciality).Distinct();
        //return query.ToList();
    }
    public static List<string> getCanadianDentistSpecialityId()
    {
        DataCalendarDataContext DCD = new DataCalendarDataContext();
        return (from m in DCD.CanadianDentists.Select(m => m.Specialty).Distinct()

                select m).ToList();

        //var query = DCD.DentistTests.Select(m => m.Speciality).Distinct();
        //return query.ToList();
    }
    public static List<string> getOldSpecialityId()
    {
        DataCalendarDataContext DCD = new DataCalendarDataContext();
        return (from m in DCD.tbl_Specialities.Select(m => m.Name).Distinct()


                select m).ToList();

        //var query = DCD.DentistTests.Select(m => m.Speciality).Distinct();
        //return query.ToList();
    }

    public static List<string> getOldDentistSpecialityId()
    {
        DataCalendarDataContext DCD = new DataCalendarDataContext();
        return (from m in DCD.tbl_DentistSpecialties.Select(m => m.specialty).Distinct()


                select m).ToList();

        //var query = DCD.DentistTests.Select(m => m.Speciality).Distinct();
        //return query.ToList();
    }
    public static bool AddDentOverallRatiind(tbl_dentOverallrating cEvent)
    {
        DataCalendarDataContext DCD = new DataCalendarDataContext();
        try
        {

            DCD.tbl_dentOverallratings.InsertOnSubmit(cEvent);
            DCD.SubmitChanges();
            //long JobID = cEvent.id;

            return true;

        }
        catch
        {
            return false;
        }
    }
    public static bool AddDentist(tbl_Dentist dentist)
    {
        DataCalendarDataContext DCD = new DataCalendarDataContext();
        try
        {
            DCD.tbl_Dentists.InsertOnSubmit(dentist);
            DCD.SubmitChanges();
            return true;

        }
        catch
        {
            return false;
        }
    }
    public static bool AddDoctorProfile(tbl_DoctorProfile dentist)
    {
        DataCalendarDataContext DCD = new DataCalendarDataContext();
        try
        {
            DCD.tbl_DoctorProfiles.InsertOnSubmit(dentist);
            DCD.SubmitChanges();
            return true;

        }
        catch
        {
            return false;
        }
    }
    public static List<string> GetReciverIds(int? id)
    {
        DataCalendarDataContext DCD = new DataCalendarDataContext();
        try
        {

            return (from p in DCD.FacePages

                    where (p.SenderID == id || p.ReceiverID==id)
                    select p.ReceiverID.ToString()).Distinct().ToList();

        }
        catch
        {
            return null;
        }

    }
    public static List<string> GetSenderIds(int? id)
    {
        DataCalendarDataContext DCD = new DataCalendarDataContext();
        try
        {

            return (from p in DCD.FacePages

                    where p.ReceiverID == id
                    select p.SenderID.ToString()).Distinct().ToList();

        }
        catch
        {
            return null;
        }

    }
    public static FacePage GetFaceGageMessage(int? id)
    {
        DataCalendarDataContext DCD = new DataCalendarDataContext();
        try
        {

            return (from p in DCD.FacePages

                    where p.Id == id
                    select p).Single();

        }
        catch
        {
            return null;
        }

    }
    public static tbl_Doctor GetReceiverDoctor(int? id)
    {
        DataCalendarDataContext DCD = new DataCalendarDataContext();
        try
        {

            return (from p in DCD.tbl_Doctors

                    where p.Id == id
                    select p).Single();

        }
        catch
        {
            return null;
        }

    }
    public static tbl_Dentist GetReceiverDentists(int? id)
    {
        DataCalendarDataContext DCD = new DataCalendarDataContext();
        try
        {

            return (from p in DCD.tbl_Dentists

                    where p.Id == id
                    select p).Single();

        }
        catch
        {
            return null;
        }

    }
    public static bool UpdateFacepage(int Id, bool name)
    {
       DataCalendarDataContext DCD = new DataCalendarDataContext(); 
        var m = (from f in DCD.FacePages
                 where f.Id == Convert.ToInt32(Id)
                 select f).Single();
        m.IsRead = name;

        try
        {
            DCD.SubmitChanges();
            return true;
        }
        catch
        {
            return false;
        }
    }
    public static bool AddReplyMessage(FacePage fp)
    {
        DataCalendarDataContext DCD = new DataCalendarDataContext();
        try
        {
            DCD.FacePages.InsertOnSubmit(fp);
            DCD.SubmitChanges();
            return true;

        }
        catch
        {
            return false;
        }
    }
}
